# 导入pymysql
import pymysql

def get_data_from_mysql(sql,database="interface_database"):
    '''
    通过sql从mysql获取数据.
    :param sql:
    :param database:
    :return:
    '''
    # 1.创建连接
    # 连接数据库肯定需要一些参数
    conn = pymysql.connect(
        host="127.0.0.1",  # host地址
        port=3306,  # 端口号
        database=database,  # 具体的数据库名称
        charset="utf8",  # 数据库编码
        user="root",
        passwd="123456"
    )

    # 2.创建一个游标-执行sql语句的工具
    cursor = conn.cursor()

    # 3.操作sql语句

    # sql1 = 'desc student;'

    cursor.execute(sql)
    # 4.从执行结果中获取数据
    data = cursor.fetchall()
    print(cursor.description)
    head_list = [t[0] for t in cursor.description]
    print(head_list)
    data_res = list(data)
    data_res.insert(0,tuple(head_list))


    # print(data_res)
    # 5.关闭游标,关闭链接
    cursor.close()
    conn.close()

    return data_res




def write_to_csv(filename,data):
    '''

    :param filename: 文件名
    :param data: 写入的数据:[(),(),...]
    :return:
    '''
    # 写入csv文件
    import csv
    # print(data2)
    with open(filename, 'w', newline='', encoding='utf-8') as fp:
        # 创建一个writer对象
        writer = csv.writer(fp)
        writer.writerows(data)



if __name__ == '__main__':
    sql = 'select id,name,age from student where id <= 104;'
    data = get_data_from_mysql(sql)
    print(data)

    write_to_csv('./data2.csv',data)
